IF EXISTS
(
	SELECT TOP *
	FROM dbo.[sysobjects]
	WHERE [id]=OBJECT_ID(N'dbo.[ExampleProcedure]') AND OBJECTPROPERTY(id, N'IsProcedure')=1
)
	BEGIN
	DROP PROCEDURE dbo.[ExampleProcedure]
	END
GO
 
CREATE PROCEDURE dbo.[ExampleProcedure]
(
	@value varchar(100)
)
AS
DECLARE @identity int
 
-- validate @value

IF @value IS NULL
	BEGIN
	RAISERROR ('The @value cannot be null.', 16, 1)
	END

IF LEN(@value) = 0
	BEGIN
	RAISERROR ('The @value cannot be empty.', 16, 1)
	END

SET @identity = (SELECT TOP 1 [Id] FROM dbo.[ExampleTable] WHERE [Value]=@value)

IF @identity IS NULL
	BEGIN
	INSERT INTO dbo.[ExampleTable] ([Value]) VALUES (@value)
	SET @identity = SCOPE_IDENTITY()
	END

SELECT @identity
 
GO